"""
MySQL数据库工具类使用示例
"""
from utils.mysql_db import db, DBObject, SqlFormat
from utils.logger import logger


def example_basic_usage():
    """基础使用示例"""
    try:
        # 1. 查询单条记录
        user = db.select_one("users", id=1)
        logger.info(f"查询到用户: {user}")
        
        # 2. 查询多条记录
        users = db.select_one_table("users", status=1)
        logger.info(f"查询到{len(users)}个活跃用户")
        
        # 3. 插入数据
        new_user = {
            "username": "testuser",
            "email": "test@example.com",
            "status": 1,
            "created_at": "2024-01-01 00:00:00"
        }
        affected_rows = db.insert_data("users", new_user)
        logger.info(f"插入数据影响行数: {affected_rows}")
        
        # 4. 更新数据
        update_data = {"status": 0}
        where_condition = {"username": "testuser"}
        affected_rows = db.update_data("users", update_data, where_condition)
        logger.info(f"更新数据影响行数: {affected_rows}")
        
        # 5. 删除数据
        affected_rows = db.delete_data("users", {"username": "testuser"})
        logger.info(f"删除数据影响行数: {affected_rows}")
        
    except Exception as e:
        logger.error(f"示例执行失败: {str(e)}")


def example_advanced_usage():
    """高级使用示例"""
    try:
        # 1. 使用SqlFormat生成复杂查询
        sql, params = SqlFormat.select_normal(
            table="users",
            data="id, username, email",
            params={"status": 1, "created_at": "2024-01-01"},
            end="ORDER BY id DESC LIMIT 10"
        )
        results = db.query(sql, params)
        logger.info(f"复杂查询结果: {results}")
        
        # 2. 执行原生SQL
        custom_sql = """
        SELECT u.username, p.title 
        FROM users u 
        LEFT JOIN posts p ON u.id = p.user_id 
        WHERE u.status = %s
        """
        results = db.query(custom_sql, [1])
        logger.info(f"原生SQL查询结果: {results}")
        
        # 3. 事务操作
        transaction_sqls = [
            ("INSERT INTO users (username, email) VALUES (%s, %s)", ["user1", "user1@test.com"]),
            ("INSERT INTO users (username, email) VALUES (%s, %s)", ["user2", "user2@test.com"]),
            ("UPDATE users SET status = %s WHERE username = %s", [1, "user1"])
        ]
        success = db.execute_transaction(transaction_sqls)
        logger.info(f"事务执行结果: {success}")
        
    except Exception as e:
        logger.error(f"高级示例执行失败: {str(e)}")


def example_multiple_databases():
    """多数据库连接示例"""
    try:
        # 连接不同数据库
        db1 = DBObject(database="database1")
        db2 = DBObject(database="database2")
        
        # 在不同数据库执行操作
        users1 = db1.select_one_table("users")
        users2 = db2.select_one_table("users")
        
        logger.info(f"数据库1用户数: {len(users1)}")
        logger.info(f"数据库2用户数: {len(users2)}")
        
        # 记得关闭连接
        db1.close()
        db2.close()
        
    except Exception as e:
        logger.error(f"多数据库示例执行失败: {str(e)}")


def example_error_handling():
    """错误处理示例"""
    try:
        # 1. 尝试查询不存在的表
        try:
            result = db.query("SELECT * FROM non_existent_table")
        except Exception as e:
            logger.error(f"查询不存在的表: {str(e)}")
        
        # 2. 尝试插入无效数据
        try:
            invalid_data = {}  # 空数据
            db.insert_data("users", invalid_data)
        except ValueError as e:
            logger.error(f"插入无效数据: {str(e)}")
        
        # 3. 尝试无条件删除（会抛出异常）
        try:
            db.delete_data("users", {})  # 空条件
        except ValueError as e:
            logger.error(f"无条件删除被阻止: {str(e)}")
            
    except Exception as e:
        logger.error(f"错误处理示例失败: {str(e)}")


if __name__ == "__main__":
    # 运行示例
    logger.info("=== 基础使用示例 ===")
    example_basic_usage()
    
    logger.info("=== 高级使用示例 ===")
    example_advanced_usage()
    
    logger.info("=== 多数据库示例 ===")
    example_multiple_databases()
    
    logger.info("=== 错误处理示例 ===")
    example_error_handling()
    
    # 关闭全局数据库连接
    if db:
        db.close() 